import pandas as pd
import os
from config.config import config_params
import util.env_utils as env_utils
import util.log_utils as log_utils
import util.db_utils as db_utils

__excel_file_path = os.path.join(env_utils.project_root, config_params['excel_file_path'])
__logger = log_utils.setup_logger(env_utils.get_relative_path_and_name(__file__))

def import_to_t_project_info_from_excel():
    conn = db_utils.get_conn()
    cursor = db_utils.get_cursor()
    # 读取 Excel 文件
    df = pd.read_excel(__excel_file_path, sheet_name='项目')
    # 将 DataFrame 中的 NaN 替换为 None
    # df = df.fillna.where(pd.notna(df), None)
    v_rows = df.values.tolist()

    v_batch_size = 1000
    v_pre_approval_date_index = 8  # 根据插入语句中字段顺序确定 pre_approval_date 索引
    v_signing_date_index = 9  # 根据插入语句中字段顺序确定 signing_date 索引
    v_signing_date2_index = 11  # 根据插入语句中字段顺序确定 signing_date2 索引
    v_creation_time_index = 12  # 根据插入语句中字段顺序确定 creation_time 索引
    v_approval_date_index = 13  # 根据插入语句中字段顺序确定 approval_date 索引
    v_termination_date_index = 14  # 根据插入语句中字段顺序确定 termination_date 索引
    for v_i in range(0, len(v_rows), v_batch_size):
        v_batch = v_rows[v_i:v_i + v_batch_size]
        for v_row in v_batch:
            if pd.isna(v_row[v_pre_approval_date_index]):
                v_row[v_pre_approval_date_index] = None
            if pd.isna(v_row[v_signing_date_index]):
                v_row[v_signing_date_index] = None
            if pd.isna(v_row[v_signing_date2_index]):
                v_row[v_signing_date2_index] = None
            if pd.isna(v_row[v_creation_time_index]):
                v_row[v_creation_time_index] = None
            if pd.isna(v_row[v_approval_date_index]):
                v_row[v_approval_date_index] = None
            if pd.isna(v_row[v_termination_date_index]):
                v_row[v_termination_date_index] = None

        v_insert_query = '''
        INSERT INTO t_project_info (
            project_code, project_name, organization, department, project_status,
            project_manager, project_manager_code, project_type, pre_approval_date,
            signing_date, signing_status, signing_date2, creation_time, approval_date,
            termination_date, pre_investment_limit, pre_investment_total, project_source,
            parent_project_code, is_main_project, expected_sales_amount, customer_name
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        '''
        try:
            cursor.executemany(v_insert_query, v_batch)
            conn.commit()
            v_end_index = v_i + min(len(v_batch), v_batch_size) - 1
            __logger.info(f'项目信息批次数据导入成功，批次范围：{v_i + 1} - {v_end_index + 1}')
        except Exception as e:
            conn.rollback()
            v_error_row_index = 0
            for v_index, v_row in enumerate(v_batch):
                try:
                    cursor.execute(v_insert_query, v_row)
                except Exception as inner_e:
                    v_error_row_index = v_i + v_index
                    v_end_index = v_i + min(len(v_batch), v_batch_size) - 1
                    __logger.error(f'项目信息批次数据导入失败，当前批次: {v_i // v_batch_size + 1}，开始记录数: {v_i + 1}，结束记录数: {v_end_index + 1}，错误数据所在行: {v_error_row_index + 2}（包含标题行），错误信息: {inner_e}')
                    __logger.error(f'错误的行数据如下： {v_row}')
                    break
            conn.rollback()